package Demo2.dao;
import Demo2.entity.Student;
import Demo2.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    //查看全部学生表
    public List<Student> stuAll(){
        List<Student> list =new ArrayList<>();
        Connection conn = DBUtil.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        String sql ="select * from s_student";
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            DBUtil.close(rs,ps,conn);
        }
        return list;
    }

    //增加
    public int add(Student s){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn = DBUtil.getConnection();
            String sql ="INSERT INTO `s_student` (`sname`, `sex`, `telephone`, `qq`, `wechat`, `gradeid`, `enterdate`, `fromcity`, `birthday`, `address`, `pass`, `idcardpic`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            ps =conn.prepareStatement(sql);
            ps.setString(1,s.getSname());
            ps.setString(2,s.getSex());
            ps.setString(3,s.getTelephone());
            ps.setString(4,s.getQq());
            ps.setString(5,s.getWechat());
            ps.setLong(6,s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8,s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10,s.getAddress());
            ps.setString(11,s.getPass());
            ps.setString(12,s.getIdcardpic());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //修改
    public int update(Student s){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="UPDATE `s_student` SET `sname` = ?, `sex` = ?, `telephone` = ?, `qq` = ?, `wechat` = ?, `gradeid` = ?, `enterdate` = ?, `fromcity` = ?, `birthday` = ?, `address` = ?, `pass` = ?, `idcardpic` = ? WHERE `stuno` = ?";
            ps =conn.prepareStatement(sql);
            ps.setString(1,s.getSname());
            ps.setString(2,s.getSex());
            ps.setString(3,s.getTelephone());
            ps.setString(4,s.getQq());
            ps.setString(5,s.getWechat());
            ps.setLong(6,s.getGradeid());
            ps.setDate(7, (Date) s.getEnterdate());
            ps.setString(8,s.getFromcity());
            ps.setDate(9, (Date) s.getBirthday());
            ps.setString(10,s.getAddress());
            ps.setString(11,s.getPass());
            ps.setString(12,s.getIdcardpic());
            ps.setLong(13,s.getStuno());
            int num = ps.executeUpdate();
            return num;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //删除
    public int del(long stuno){
        Connection conn =null;
        PreparedStatement ps =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="delete from s_student where stuno=?";
            ps =conn.prepareStatement(sql);
            ps.setLong(1,stuno);
            int i = ps.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(ps);
            DBUtil.close(conn);
        }
        return 0;
    }

    //查询单个学生
    public Student getStu(long stuno){
        Connection conn =null;
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="select * from `s_student` where `stuno` = ?";
            ps =conn.prepareStatement(sql);
            ps.setLong(1,stuno);
            rs = ps.executeQuery();
            while(rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                return st;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(rs,ps,conn);
        }
        return null;
    }

    //分页查询
    public List<Student> limitStu(int pangNo,int pangSize){
        List<Student> list =new ArrayList<>();
        Connection conn =null;
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            conn =DBUtil.getConnection();
            String sql ="select * from s_student order by stuno desc limit ?,?";
            ps =conn.prepareStatement(sql);
            ps.setInt(1,(pangNo-1)*pangSize);
            ps.setInt(2,pangSize);
            rs = ps.executeQuery();
            while (rs.next()){
                Student st =new Student();
                st.setStuno(rs.getLong("stuno"));
                st.setSname(rs.getString("sname"));
                st.setSex(rs.getString("sex"));
                st.setTelephone(rs.getString("telephone"));
                st.setQq(rs.getString("qq"));
                st.setWechat(rs.getString("wechat"));
                st.setGradeid(rs.getLong("gradeid"));
                st.setEnterdate(rs.getDate("enterdate"));
                st.setFromcity(rs.getString("fromcity"));
                st.setBirthday(rs.getDate("birthday"));
                st.setAddress(rs.getString("address"));
                st.setPass(rs.getString("pass"));
                st.setIdcardpic(rs.getString("idcardpic"));
                list.add(st);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //模糊查询
    public List<Student> search(int pageNo, int pageSize, String sname, String qq, String phone, Date enterStart, Date enterEnd) {
        List<Student> datas = new ArrayList<>();
        Connection connection = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Object> params=new ArrayList<>();
        String sql = "select * from s_student where 1=1 ";
        if (sname != null && !sname.equals(""))
        {
            sql += " and sname like concat('%',?,'%')";
            params.add(sname);
        }
        if (qq != null && !qq.equals(""))
        {
            sql += " and qq like concat('%',?,'%')";
            params.add(qq);
        }
        if (phone != null && !phone.equals(""))
        {
            sql += " and telephone like concat('%',?,'%')";
            params.add(phone);
        }
        if (enterStart != null  && enterEnd != null )
        {
            sql += " and birthday between ? and ?";
            params.add(enterStart);
            params.add(enterEnd);
        }
        sql += " limit ?,?";
        params.add((pageNo - 1) * pageSize);
        params.add(pageSize);
        System.out.println(sql);
        try {
            ps = connection.prepareStatement(sql);
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            ps.setString(1, sname);
            ps.setString(2, qq);
            ps.setString(3, phone);
            ps.setDate(4,enterStart);
            ps.setDate(5,enterEnd);
            ps.setInt(6, (pageNo - 1) * pageSize);
            ps.setInt(7, pageSize);

            rs = ps.executeQuery();
            while (rs.next()) {
                Student student = new Student();
                student.setStuno(rs.getInt("stuno"));
                student.setSname(rs.getString("sname"));
                student.setSex(rs.getString("sex"));
                student.setTelephone(rs.getString("telephone"));
                student.setQq(rs.getString("qq"));
                student.setWechat(rs.getString("wechat"));
                student.setGradeid(rs.getInt("gradeid"));
                student.setEnterdate(rs.getDate("enterdate"));
                student.setBirthday(rs.getDate("birthday"));
                student.setFromcity(rs.getString("fromcity"));
                student.setAddress(rs.getString("address"));
                student.setPass(rs.getString("pass"));
                datas.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(rs, ps, connection);
        }
        return datas;
    }
}
